# -*- coding: utf-8 -*-

from openpyxl import load_workbook # 可以用来载入已有数据表格
from openpyxl import Workbook # 可以用来处理新的数据表格
import datetime # 可以用来处理时间相关的数据

def combine():
    '''
    该函数可以用来处理原数据文件：
    1. 合并表格并写入的 combine 表中
    2. 保存原数据文件
    '''
    wb = load_workbook('courses.xlsx')
    ws1 = wb["students"]
    ws2 = wb["time"]
    ws3 = wb.create_sheet("combine")

    dic1 = {}
    dic2 = {}

    # 两个sheet分别存储在两个字典中
    for i in range(2, ws1.max_row + 1):
        dic1.setdefault(ws1.cell(row=i, column=1).value, {'name': '', 'people': '', })
        dic1[ws1.cell(row=i, column=1).value]['name'] = ws1.cell(row=i, column=2).value
        dic1[ws1.cell(row=i, column=1).value]['people'] = ws1.cell(row=i, column=3).value

    dic1 = [(k, dic1[k]) for k in sorted(dic1.keys())]

    for i in range(2, ws2.max_row + 1):
        dic2.setdefault(ws2.cell(row=i, column=1).value, {'time': '',})
        dic2[ws2.cell(row=i, column=1).value]['time'] = ws2.cell(row=i, column=3).value

    dic2 = [(k,dic2[k]) for k in sorted(dic2.keys())]

    ws3.cell(row=1, column=1).value = '创建时间'
    ws3.cell(row=1, column=2).value = '课程名称'
    ws3.cell(row=1, column=3).value = '学习人数'
    ws3.cell(row=1, column=4).value = '学习时间'

    iRow = 1

    # 遍历dict1
    for r in dic1:
        iRow += 1
        ws3.cell(row=iRow, column=1).value = r[0]
        ws3.cell(row=iRow, column=2).value = r[1]['name']
        ws3.cell(row=iRow, column=3).value = r[1]['people']
        ws3.cell(row=iRow, column=4).value = dic2[iRow-2][1]['time']

    wb.save("courses.xlsx")


def split():
    '''
    该函数可以用来分割文件：
    1. 读取 combine 表中的数据
    2. 将数据按时间分割
    3. 写入不同的数据表中
    '''
    wb = load_workbook('courses.xlsx')
    combine = wb["combine"]
    dic1 = {}
    for i in range(2, combine.max_row + 1):
        dic1.setdefault(combine.cell(row=i, column=1).value, {'name': '', 'people': '','time':'' })
        dic1[combine.cell(row=i, column=1).value]['name'] = combine.cell(row=i, column=2).value
        dic1[combine.cell(row=i, column=1).value]['people'] = combine.cell(row=i, column=3).value
        dic1[combine.cell(row=i, column=1).value]['time'] = combine.cell(row=i, column=4).value
    d1 = []
    for i in range(2013,2017):
        d1 = [(k, dic1[k]) for k in dic1 if k.year == i]
        createxls(d1,str(i))


def createxls(dic1,n):
    wb = Workbook()
    ws3 = wb.active
    ws3.title = n
    ws3.cell(row=1, column=1).value = '创建时间'
    ws3.cell(row=1, column=2).value = '课程名称'
    ws3.cell(row=1, column=3).value = '学习人数'
    ws3.cell(row=1, column=4).value = '学习时间'

    iRow = 1

    # 遍历dict1
    for r in dic1:
        iRow += 1
        ws3.cell(row=iRow, column=1).value = r[0]
        ws3.cell(row=iRow, column=2).value = r[1]['name']
        ws3.cell(row=iRow, column=3).value = r[1]['people']
        ws3.cell(row=iRow, column=4).value = r[1]['time']
    wb.save(n+".xlsx")

# 执行
if __name__ == '__main__':
    combine()
    split()